-- @author tongdanyu
-- @date 20230710
-- C侧罗盘数据指标-日活、简历、流量、留存

set spark.sql.crossJoin.enabled = true ;

with loginuv as 
(
    --app总登陆uv
    select  
        'all' as source_type      
        ,count(distinct userid) as login_uv        
    from hdp_lbg_supin_zplisting.dm_zp_wb_user_activeuser_inc
    where dt = '${dateSuffix}'
),

resume_jl as
(
    --简历
    select 
        'all'as source_type      
        ,max(login_no_resume_uv) as login_no_resume_uv
        ,max(new_resume_uv) as new_resume_uv         
    from hdp_lbg_supin_zplisting.app_zp_wb_n_resume_user_city1_cate
    where dt ='${dateSuffix}' 
    and city1_name = 'all'
    and cate2_name = 'all'
    and cate3_name = 'all'
    and platform_name = 'all'
    and resume_fbly = 'all'
),

nflow as 
(
    --流量
    select 
         source_type       
        ,max(search_as) as search_as
        ,max(search_uv) as search_uv
        ,max(exp_as) as exp_as
        ,max(exp_uv) as exp_uv
        ,max(click_epv) as click_epv
        ,max(click_uv) as click_uv
        ,sum(im_cnt+tel_cnt+delivery_cnt) as link_cnt
        ,max(link_uv) as link_uv
        ,sum(im_cnt_bsend) as link_cnt_bsend
        ,max(im_uv_bsend) as link_uv_bsend
        ,sum(im_cnt_csend+tel_cnt+delivery_cnt) as link_cnt_csend
        ,max(link_uv_csend) as link_uv_csend
        ,sum(im_cnt_valid+tel_cnt_valid+delivery_cnt) as link_cnt_valid
        ,max(link_uv_valid) as link_uv_valid
    from  
    (
        select
             'all' as source_type
            ,count(distinct if(info_id>0,concat(sid,cast(info_id as String),slot),null)) as search_as
            ,count(distinct ukey) as search_uv
            ,count(distinct if(has_exposure=1 and info_id>0,concat(sid,cast(info_id as String),slot),null)) as exp_as
            ,count(distinct if(has_exposure=1,ukey,null)) as exp_uv
            ,count(distinct if(has_infodetail=1 and  info_id>0,sid,null)) as click_epv
            ,count(distinct if(has_infodetail=1,ukey,null)) as click_uv
            ,count(distinct if(has_im=1 and session_id<>'-',session_id,null)) as im_cnt
            ,count(distinct if(has_im=1 and bc_type='B' and session_id<>'-',session_id,null)) as im_cnt_bsend
            ,count(distinct if(has_im=1 and bc_type='C' and session_id<>'-',session_id,null)) as im_cnt_csend
            ,count(distinct if(has_im=1 and has_reply=1 and session_id<>'-',session_id,null)) as im_cnt_valid
            ,sum(if(has_tel=1,tel_cnt,0)) as tel_cnt
            ,sum(if(has_tel=1 and tel_conn_cnt=1,tel_cnt,0)) as tel_cnt_valid
            ,sum(if(has_delivery=1,delivery_cnt,0)) as delivery_cnt
            ,count(distinct if(has_im=1 and bc_type='B' and userid>0,userid,null)) as im_uv_bsend
            ,count(distinct if((has_im=1 and bc_type='C' or has_tel+has_delivery>1) and userid>0,userid,null)) as link_uv_csend
            ,count(distinct if(has_reply+has_delivery+tel_conn_cnt>1 and userid>0,userid,null)) as link_uv_valid
            ,count(distinct if(has_im+has_delivery+has_tel>1 and userid>0,userid,null)) as link_uv
        from hdp_lbg_supin_zplisting.dm_zp_wb_n_flow
        where dt ='${dateSuffix}'
        and platform = 3 
        and has_disp = 1
    ) tb 
    group by source_type
),

retention as 
(
    --留存
    select     
         'all' as source_type        
        ,max(if(datediff(dt,active_date)=1,retentiondau1,0)) as dau1
        ,max(if(datediff(dt,active_date)=6,retentiondau7,0)) as dau7
        ,max(if(datediff(dt,active_date)=1,new_dau1,0)) as new_dau1
        ,max(if(datediff(dt,active_date)=6,new_dau7,0)) as new_dau7
    from 
    (
        select 
             from_unixtime(unix_timestamp(active_date_str,'yyyyMMdd'),'yyyy-MM-dd') as active_date
            ,from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd') as dt
            ,retentiondau1  
            ,retentiondau7
            ,new_dau1   
            ,new_dau7
        from hdp_lbg_supin_zplisting.app_zp_wb_user_activeuser_retention_d_inc
        where dt = '${dateSuffix}'
        and city1='all'
    ) ta
)

insert overwrite table hdp_lbg_supin_zplisting.app_zp_n_luopan_data partition(dt = '${dateSuffix}')
select
     cu.platform
    ,cu.source_type
    ,cu.dau   
    ,cu.dau_full
    ,cu.dau_part
    ,nvl(lu.login_uv,0) as login_uv
    ,nvl(jl.login_no_resume_uv,0) as login_no_resume_uv 
    ,nvl(jl.new_resume_uv,0) as new_resume_uv
    ,nvl(ll.search_as,0) as search_as
    ,nvl(ll.search_uv,0) as search_uv
    ,nvl(ll.exp_as,0) as exp_as
    ,nvl(ll.exp_uv,0) as exp_uv
    ,nvl(ll.click_epv,0) as click_epv
    ,nvl(ll.click_uv,0) as click_uv
    ,nvl(ll.link_cnt,0) as link_cnt
    ,nvl(ll.link_uv,0) as link_uv
    ,nvl(ll.link_cnt_bsend,0) as link_cnt_bsend
    ,nvl(ll.link_uv_bsend,0) as link_uv_bsend
    ,nvl(ll.link_cnt_csend,0) as link_cnt_csend
    ,nvl(ll.link_uv_csend,0) as link_uv_csend
    ,nvl(ll.link_cnt_valid,0) as link_cnt_valid
    ,nvl(ll.link_uv_valid,0) as link_uv_valid
    ,nvl(lc.dau1,0) as dau1
    ,nvl(lc.dau7,0) as dau7
    ,nvl(lc.new_dau1,0) as new_dau1
    ,nvl(lc.new_dau7,0) as new_dau7    
from
(
    --分平台dau
    select 
        nvl(platform,'all') platform
        ,'all' as source_type
        ,if(platform in ('pc','m'),'n','y') as flag --pc，m条不关联其他指标标记
        ,sum(if(cate1_name='ALL',uv,0)) as dau
        ,sum(if(cate1_name='全职招聘',uv,0)) as dau_full
        ,sum(if(cate1_name='兼职招聘',uv,0)) as dau_part
    from hdp_fin_ba_defaultdb.dw_ba_report_netflow_inc_d
    where partition_dt = '${dateSuffix}' 
    and  brand = "58" 
    and bg = "HRG"
    and bu= 'zhaopin'
    and city1_name = "ALL"
    and cate2_name='ALL'
    and platform in ('pc','m','app') 
    and cate1_name <> '其他'
    group by platform 
    with cube

    union all 

    --app分渠道dau
    select  
        'app' as platform
        ,nvl(source_type,'-') as source_type
        ,'n' as flag
        ,count(distinct imei) as dau 
        ,count(if(type_map['full']=1,imei,null)) as dau_full 
        ,count(if(type_map['part']=1,imei,null)) as dau_part 
    from hdp_lbg_supin_zplisting.dm_zp_wb_user_activeuser_inc
    where dt = '${dateSuffix}'
    group by source_type
) cu 
left join loginuv lu on cu.source_type=lu.source_type and cu.flag='y'
left join resume_jl jl on cu.source_type=jl.source_type and cu.flag='y'
left join nflow ll on cu.source_type=ll.source_type and cu.flag='y'
left join retention lc on cu.source_type=lc.source_type and cu.flag='y'




